package utils;

/**
 * Created by cxland on 2017/9/22.
 */

import dto.ConfDto;
import dto.ConfigDto;
import dto.TableDto;
import dto.TableListDto;
import jws.dal.common.DbType;
import org.yaml.snakeyaml.Yaml;

import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.util.*;

public class DBUtils {
private static Connection connection =null;
    /**
     * 创建数据库链接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection(Map<String,String> dbConf) throws ClassNotFoundException, SQLException{
        Connection conn = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = dbConf.get("url")+dbConf.get("dbName")+"?useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true";
        String user = dbConf.get("user");
        String password = dbConf.get("password");
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        connection = conn;
        return conn;
    }

    /**
     *   获取数据库表的对象
     * @param tableName
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static TableListDto  getTable( String tableName) throws SQLException,ClassNotFoundException{
        Connection conn = connection;
        String sql = "select * from "+tableName+" LIMIT 1 ";

        PreparedStatement stmt;
        stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData data = rs.getMetaData();
        TableListDto listDto = new TableListDto();
        List<TableDto> list = new ArrayList<>();

        //获取主键
        List<String> keyList = getPrimaryKeys(tableName);
        if(keyList.size()>1){
            listDto.setDoublePK(true);
        }

        //获取其他字段信息
        for (int i = 1; i <= data.getColumnCount(); i++) {
            TableDto dto = new TableDto();
            int isNullable = data.isNullable(i);
            String columnName = data.getColumnName(i);
            String columnTypeName = data.getColumnTypeName(i);
            String columnClassName = data.getColumnClassName(i);

            String name = data.getTableName(i);

            listDto.setTableName(name);

            dto.setTableName(name);
            dto.setColumnName(columnName);
            dto.setType(columnTypeName);
            dto.setTypeClass(columnClassName);
            dto.setIsNullable(isNullable==0 ? false : true);

            for (String key :keyList){
                if(columnName.equals(key)){
                    dto.setPrimaryKeys(true);
                }
            }
            list.add(dto);
            }
            listDto.setList(list);
        return listDto;
    }

    /**
     * 根据表名获取表的主键
     * @param tableName
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static List<String> getPrimaryKeys(String tableName) throws SQLException, ClassNotFoundException{
        Connection conn = connection;
        DatabaseMetaData dbMetaData = conn.getMetaData();
        ResultSet pkRSet = dbMetaData.getPrimaryKeys(null, null, tableName);
        List<String> keyList = new ArrayList<>();
        while( pkRSet.next() ) {
            String primaryKeys = (String) pkRSet.getObject(4);
            keyList.add(primaryKeys);
        }
        return keyList;
    }

    /**
     * 获取数据库所有的表名
     * @return
     * @throws Exception
     */
    public static List<String>getAllTableName(Map<String,String> map)throws Exception{
        Connection conn = getConnection(map);

        DatabaseMetaData dmd = conn.getMetaData();
        String[] types = {"TABLE"};
        ResultSet rs = dmd.getTables(null, null, "%", types);

        List<String>list = new LinkedList<>();
        while(rs.next()){
            list.add(rs.getString(3));
        }
        rs.close();

        return list;
    }

    /**
     * 获取允许生成代码的数据库表
     * @param confDto
     * @return
     * @throws Exception
     */
    public static List<String> getNeedGenerateTable(ConfDto confDto)throws Exception{
        List<String>  allList = getAllTableName(confDto.getDbConf());
        String[]  confList = confDto.getIgnoreTable();
        List<String> needList = getAllTableName(confDto.getDbConf());
        System.out.println("-------------------------------------");
       // System.out.println();
        for(String table :allList){
            Boolean need = false;
            for (String str: confList) {
                if (table.equals(str)){
                    needList.remove(table);
                    need = true;
                    break;
                }
            }
            if(!need){
                System.out.println(table);
                System.out.println();
            }
        }
        //System.out.println();
        System.out.println("------------------------------------");
        return needList;
    }
}